A transaction is a group of events that occur between any of the following events:

·         Connecting to database

·         Disconnecting to database

·         Committing changes to the database table

·         Rollback

“A series of one or more SQL statements that are logically related or a series of operations performed on table data is termed as a Transaction”.

A transaction begins with the first executable SQL statement after a commit, rollback or connection made to the database engine. All changes made to a table data via a transaction are made or undone at one instance.

How to close the transaction

A transaction can be closed by using either a commit or a rollback statement. By using these statements, table data can be changed or all the changes made to the table data undone.

COMMIT

A COMMIT ends the current transaction and makes permanent any changes made during the transaction. All transactional locks acquired on tables are released.

Syntax

COMMIT

ROLLBACK

A ROLLBACK does exactly the opposite of commit. It ends the transaction but undoes any changes made during the transaction. All transactional locks acquired on tables are released.

Syntax:

ROLLBACK [WORK][TO [SAVEPOINT]<SavepointName>]

Where

WORK   Is optional and is provided for ANSI compatibility

SAVEPOINT   Is optional and is used to rollback a transaction partially (it marks and saves the current point in the processing of a transaction).

SavepontName   Is a savepoint created during the current transaction

Rollback must be done with or without the savepoint.